﻿using Dapper;
using MX.Core.Log;
using MX.Core.ORM;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using UserCenter.Models;

namespace UserCenter.BLL
{
    public static class MemberBLL
    {
        /// <summary>
        /// 获取
        /// </summary>
        /// <param name="merchantId"></param>
        /// <returns></returns>
        public static List<Member> List(int merchantId)
        {
            string strSql = "Select * from Member where MerchantId=@MerchantId";
            using (var conn = DatabaseHelper.Get())
            {
                return conn.Query<Member>(strSql, new { MerchantId = merchantId }).AsList();
            }
        }


        /// <summary>
        /// 创建一个用户
        /// </summary>
        /// <param name="merchantId"></param>
        /// <param name="nickName"></param>
        /// <param name="avaterUrl"></param>
        /// <returns></returns>
        public static long Create(int merchantId, string userName, string avaterUrl)
        {
            string strSql = @"INSERT INTO member
                                            (MerchantId,
                                             UserName,
                                             AvatarUrl,
                                             CreateTime,
                                             LastLoginTime,
                                             LoginStatus,
                                             LoginErrorCount)
                                VALUES      ( @MerchantId,
                                              @UserName,
                                              @AvatarUrl,
                                              @CreateTime,
                                              @LastLoginTime,
                                              1,
                                              0)";
            using (var conn = DatabaseHelper.Get())
            {
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        long memberId = 0;
                        var has = conn.Execute(strSql, new
                        {
                            MerchantId = merchantId,
                            UserName = userName,
                            AvatarUrl = avaterUrl,
                            CreateTime = DateTime.Now,
                            LastLoginTime = DateTime.Now
                        }, transaction: tran) > 0;
                        if (has)
                        {
                            memberId = conn.ExecuteScalar<long>("SELECT LAST_INSERT_ID()", transaction: tran);


                        }
                        tran.Commit();
                        return memberId;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        Logger.GetLogger(nameof(MemberBLL)).Error(ex, "");
                        return 0;
                    }
                }
            }
        }

        public static MemberLayerLink TopLayer(int top, long memberId)
        {
            string strSql = "SELECT * FROM member_layer WHERE  MemberId=@MemberId";
            using (var conn = DatabaseHelper.Get())
            {
                void dataFun(int layer, MemberLayerLink node)
                {
                    if (top < 1) return;
                    var parents = conn.Query<MemberLayer>(strSql, new { MemberId = memberId }).AsList();
                    node.Parents.AddRange(parents.Select(p => new MemberLayerLink() { MemberId = p.ParentId }));
                    node.Parents.AsParallel().ForAll(ap =>
                    {
                        dataFun(layer - 1, ap);
                    });
                };
                MemberLayerLink root = new MemberLayerLink() { MemberId = memberId };
                dataFun(top, root);
                return root;
            }
        }


        public static string Ext(long memberId)
        {
            using (var conn = DatabaseHelper.Get())
            {
                return conn.ExecuteScalar<string>("SELECT Ext  FROM member_extend WHERE MemberId=@MemberId", new { MemberId = memberId });
            }
        }

        public static bool SetExt(long memberId, string json, bool exists)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = exists ? "update member_extend set Ext=@Ext where MemberId=@MemberId" :
                    "insert into member_extend (MemberId,Ext) values (@MemberId,@Ext)";
                return conn.Execute(strSql, new { Ext = json, MemberId = memberId }) > 0;
            }
        }

        public static bool CheckMember(int merchantId, long memberId)
        {
            if (merchantId < 1 || memberId < 1) return false;
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select count(1) from member where MerchantId=@MerchantId and MemberId=@MemberId";
                return conn.ExecuteScalar<int>(strSql, new { MerchantId = merchantId, MemberId = memberId }) > 0;
            }
        }


        public static int CheckMember(int merchantId, List<long> memberIds)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select count(1) from member where MerchantId=@MerchantId and MemberId in @MemberIds";
                return conn.ExecuteScalar<int>(strSql, new { MerchantId = merchantId, MemberIds = memberIds });
            }
        }

        public static bool CheckUserName(string userName)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select count(1) from member where UserName=@UserName ";
                return conn.ExecuteScalar<int>(strSql, new { UserName = userName }) > 0;
            }
        }

        public static long GetParentId(long memberId)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select ParentId from member_layer where MemberId=@MemberId LIMIT 1";
                return conn.QueryFirstOrDefault<long>(strSql, new { MemberId = memberId });
            }
        }




        public static bool SetParent(long memberId, long parentId, int countLayer)
        {
            //TODO 此处会存在索引效率问题
            using (var conn = DatabaseHelper.Get())
            {
                // 注意delete之后进行insert有可能发生死锁, 因为delete可能会持有区间锁, 而区间锁是可重入的(只要不是插入数据),使用ReadCommitted隔离解决,但是此处不存在此区间锁
                using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        string strSql = "delete from member_layer where MemberId=@MemberId  ";
                        conn.Execute(strSql, new
                        {
                            MemberId = memberId
                        }, transaction: tran);

                        strSql = "insert into member_layer (MemberId,ParentId,CreateTime) values (@MemberId,@ParentId,@CreateTime) ";
                        conn.Execute(strSql, new
                        {
                            MemberId = memberId,
                            ParentId = parentId,
                            CreateTime = DateTime.Now
                        }, transaction: tran);



                        var currentMemberId = parentId;
                        bool isChild = true;
                        int count = 0;
                        while (currentMemberId > 0 && count < countLayer)
                        {
                            if (conn.ExecuteScalar<int>("select count(1) from member_layer_count where MemberId=@MemberId", new { MemberId = currentMemberId }) < 1)
                            {
                                conn.Execute(@"INSERT INTO  `member_layer_count` (
                                              `MemberId`,
                                              `ChildCount`,
                                              `GroupCount`,
                                              `LastUpdateTime`
                                            ) 
                                            VALUES
                                              (
                                                @MemberId,
                                                @ChildCount,
                                                @GroupCount,
                                                @LastUpdateTime
                                              ) ", new MemberLayerCount() { ChildCount = isChild ? 1 : 0, GroupCount = 1, LastUpdateTime = DateTime.Now, MemberId = currentMemberId });
                            }
                            else
                            {
                                conn.Execute($"Update member_layer_count set {(isChild ? "ChildCount=ChildCount+1," : "")}GroupCount=GroupCount+1,LastUpdateTime=now() where MemberId=@MemberId", new { MemberId = currentMemberId });
                            }
                            strSql = "select ParentId from member_layer where MemberId=@MemberId LIMIT 1";
                            currentMemberId = conn.QueryFirstOrDefault<long>(strSql, new { MemberId = currentMemberId });
                            isChild = false;
                            count++;
                        }
                        tran.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        Logger.GetLogger(nameof(MemberBLL)).Error(ex, "");
                        return false;
                    }
                }

            }
        }

        public static bool UpdateUserName(long memberId, string phone)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "update member set UserName=@UserName where MemberId=@MemberId";
                return conn.Execute(strSql, new { MemberId = memberId, UserName = phone }) > 0;
            }
        }

        public static List<long> GetChild(long memberId)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select MemberId from member_layer where ParentId=@MemberId";
                return conn.Query<long>(strSql, new { MemberId = memberId }).AsList();
            }

        }


        public static int GetChildCount(long memberId)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select count(1) from member_layer where ParentId=@MemberId";
                return conn.Query<int>(strSql, new { MemberId = memberId }).FirstOrDefault();
            }

        }

        public static List<int> RoleIds(long memberId)
        {
            string strSql = "SELECT  RoleId  FROM  `member_role`  WHERE `MemberId` = @MemberId ";
            using (var conn = DatabaseHelper.Get())
            {
                return conn.Query<int>(strSql, new
                {
                    MemberId = memberId
                }).AsList();
            }
        }

        public static List<Role> Roles(int merchantId, long memberId)
        {
            string strSql = @"SELECT 
                              r.* 
                            FROM
                              `member_role` AS mu 
                              LEFT JOIN `role` AS r 
                                ON r.`RoleId` = mu.`RoleId` 
                            WHERE r.`MerchantId` = @MerchantId 
                              AND mu.`MemberId` = @MemberId ";
            using (var conn = DatabaseHelper.Get())
            {
                return conn.Query<Role>(strSql, new
                {
                    MerchantId = merchantId,
                    MemberId = memberId
                }).AsList();
            }

        }

        public static Member Info(long memberId)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = @"SELECT  * from `member` where MemberId=@MemberId";
                return conn.QueryFirstOrDefault<Member>(strSql, new { MemberId = memberId });
            }
        }

        public static List<Member> InfoList(List<long> memberIds)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = @"SELECT  * from `member` where MemberId in @MemberIds";
                return conn.Query<Member>(strSql, new { MemberIds = memberIds }).AsList();
            }
        }

        public static Member Info(int merchantId, string userName)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = @"SELECT  * from member where MerchantId=@MerchantId and UserName=@UserName";
                return conn.QueryFirstOrDefault<Member>(strSql, new { MerchantId = merchantId, UserName = userName });
            }
        }



        public static bool CreatePassword(long memberId, byte type, string password)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "insert into member_password(MemberId,Type,Password ) values (@MemberId,@Type,@Password )";
                return conn.Execute(strSql, new MemberPassword { MemberId = memberId, Type = type, Password = password }) > 0;
            }
        }

        public static bool UpdatePassword(long memberId, byte type, string password)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "update member_password set Password=@Password where MemberId=@MemberId and Type=@Type";
                return conn.Execute(strSql, new MemberPassword { MemberId = memberId, Type = type, Password = password }) > 0;
            }
        }

        public static MemberPassword Password(long memberId, byte type)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select * from member_password where MemberId=@MemberId and Type=@Type";
                return conn.QuerySingleOrDefault<MemberPassword>(strSql, new { MemberId = memberId, Type = type });
            }
        }

        public static List<MemberPassword> Passwords(long memberId)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select * from member_password where MemberId=@MemberId";
                return conn.Query<MemberPassword>(strSql, new { MemberId = memberId }).AsList();
            }
        }

        public static MemberAuthToken Token(string token)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select * from member_auth_token where Token=@Token";
                return conn.QuerySingleOrDefault<MemberAuthToken>(strSql, new { Token = token });
            }
        }


        public static List<MemberAuthToken> TokenList(List<string> tokenList)
        {
            using (var conn = DatabaseHelper.Get())
            {
                string strSql = "select * from member_auth_token where Token in @Tokens";
                return conn.Query<MemberAuthToken>(strSql, new { Tokens = tokenList }).AsList();
            }
        }

        public static bool SetRoles(long memberId, List<int> addRoles, List<int> removeRoles)
        {
            using (var conn = DatabaseHelper.Get())
            {
                //delete之后进行insert有可能发生死锁, 因为delete可能会持有区间锁, 而区间锁是可重入的(只要不是插入数据),使用ReadCommitted隔离解决
                using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        string strSql = "delete from member_role where MemberId=@MemberId and RoleId=@RoleId";
                        foreach (var roleId in addRoles)
                        {
                            conn.Execute(strSql, new
                            {
                                MemberId = memberId,
                                RoleId = roleId
                            }, transaction: tran);
                        }
                        strSql = "insert into member_role (MemberId,RoleId,CreateTime) values (@MemberId,@RoleId,@CreateTime) ";
                        foreach (var roleId in removeRoles)
                        {
                            conn.Execute(strSql, new
                            {
                                MemberId = memberId,
                                RoleId = roleId,
                                CreateTime = DateTime.Now
                            }, transaction: tran);
                        }
                        tran.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        Logger.GetLogger(nameof(MemberBLL)).Error(ex, "");
                        return false;
                    }
                }
            }
        }

        public static bool Auth(long memberId, string token, string ip, string deviceId, string deviceName, DeviceType deviceType, AuthSource authSource)
        {
            using (var conn = DatabaseHelper.Get())
            {
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        string strSql = "select * from member_auth_token where MemberId=@MemberId and DeviceId=@DeviceId";
                        var authToken = conn.QuerySingleOrDefault<MemberAuthToken>(strSql, new
                        {
                            MemberId = memberId,
                            DeviceId = deviceId
                        }, transaction: tran);
                        if (authToken != null)
                        {
                            strSql = "delete from member_auth_token where Token=@Token";
                            conn.Execute(strSql, new { Token = authToken.Token }, transaction: tran);
                        }
                        strSql = @"INSERT INTO `member_auth_token` (
                                                              `Token`,
                                                              `MemberId`,
                                                              `DeviceId`,
                                                              `DeviceName`,
                                                              `CreateTime`,
                                                              `ExpiredTime`,
                                                              `DeviceType`,
                                                              `AuthSource`
                                                            ) 
                                                            VALUES
                                                              (
                                                                @Token,
                                                                @MemberId,
                                                                @DeviceId,
                                                                @DeviceName,
                                                                @CreateTime,
                                                                @ExpiredTime,
                                                                @DeviceType,
                                                                @AuthSource
                                                              ) ";
                        conn.Execute(strSql, new MemberAuthToken
                        {
                            AuthSource = authSource,
                            CreateTime = DateTime.Now,
                            DeviceId = deviceId,
                            DeviceName = deviceName,
                            DeviceType = deviceType,
                            ExpiredTime = DateTime.Now.AddDays(7),
                            MemberId = memberId,
                            Token = token
                        }, transaction: tran);
                        tran.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        Logger.GetLogger(nameof(MemberBLL)).Error(ex, "");
                        tran.Rollback();
                        return false;
                    }
                }
            }
        }

        public static bool CombinAccount(string userName, long mainMemberId, List<long> combinMemberIds)
        {
            using (var conn = DatabaseHelper.Get())
            {
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        var allMemberIds = new List<long>();
                        allMemberIds.Add(mainMemberId);
                        allMemberIds.AddRange(combinMemberIds);
                        conn.Execute("update `member` set UserName=CONCAT('_backup_',UserName) where MemberId in @MemberIds", new { MemberIds = allMemberIds }, transaction: tran);
                        conn.Execute("update `member` set UserName=@UserName where MemberId=@MemberId", new { MemberId = mainMemberId, UserName = userName }, transaction: tran);
                        conn.Execute("update wx_login_info set MemberId=@MemberId where MemberId in  @MemberIds", new { MemberId = mainMemberId, MemberIds = combinMemberIds }, transaction: tran);
                        tran.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        Logger.GetLogger(nameof(MemberBLL)).Error(ex, "");
                        tran.Rollback();
                        return false;
                    }
                }
            }
        }

        public static bool SetAvater(long memberId, string url)
        {
            using (var conn = DatabaseHelper.Get())
            {
                return conn.Execute("update `member` set AvatarUrl=@AvatarUrl where MemberId=@MemberId",
                     new
                     {
                         AvatarUrl = url,
                         MemberId = memberId
                     }) > 0;
            }
        }

        static public List<MemberLayerCount> LayerCountList(List<long> memberIdList)
        {
            using (var conn = DatabaseHelper.Get())
            {
                return conn.Query<MemberLayerCount>("select * from member_layer_count where MemberId in @MemberIds", new { MemberIds = memberIdList }).AsList();
            }
        }


    }
}
